package cn.iocoder.an.module.business.utils.sql;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.regex.Pattern;

/**
 * 动态SQL执行工具类
 * 支持多数据源切换、SQL安全校验、参数化查询等功能
 */
@Component
public class DynamicSqlExecutor {

    private final Map<String, JdbcTemplate> jdbcTemplateMap = new HashMap<>();
    private final ObjectMapper objectMapper = new ObjectMapper();

    // 预定义的合法数据源列表
    private final Set<String> validDataSources = new HashSet<>();

    // 危险SQL关键字列表
    private static final String[] DANGEROUS_KEYWORDS = {
            "INSERT", "UPDATE", "DELETE", "DROP", "CREATE", "ALTER",
            "EXEC", "CALL", "UNION", "TRUNCATE", "MERGE", "REPLACE"
    };

    // SQL安全校验正则
    private static final Pattern SQL_PATTERN = Pattern.compile(
            ".*\\b(" + String.join("|", DANGEROUS_KEYWORDS) + ")\\b.*",
            Pattern.CASE_INSENSITIVE
    );

    @Autowired
    private Environment environment;

    /**
     * 初始化数据源配置
     */
    public void initializeDataSources() {
        // 从配置中读取数据源配置
        String[] dataSourceKeys = {"db1", "db2", "db3"}; // 可根据配置动态加载

        for (String key : dataSourceKeys) {
            try {
                String url = environment.getProperty("spring.datasource." + key + ".url");
                String username = environment.getProperty("spring.datasource." + key + ".username");
                String password = environment.getProperty("spring.datasource." + key + ".password");
                String driver = environment.getProperty("spring.datasource." + key + ".driver-class-name");

                if (url != null && username != null && password != null) {
                    DataSource dataSource = createDataSource(url, username, password, driver);
                    jdbcTemplateMap.put(key, new JdbcTemplate(dataSource));
                    validDataSources.add(key);
                    System.out.println("数据源 [" + key + "] 初始化成功");
                }
            } catch (Exception e) {
                System.err.println("数据源 [" + key + "] 初始化失败: " + e.getMessage());
            }
        }

        if (jdbcTemplateMap.isEmpty()) {
            throw new RuntimeException("没有配置任何有效的数据源");
        }
    }

    /**
     * 创建数据源
     */
    private DataSource createDataSource(String url, String username, String password, String driver) {
        com.zaxxer.hikari.HikariConfig config = new com.zaxxer.hikari.HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setDriverClassName(driver != null ? driver : "com.mysql.cj.jdbc.Driver");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.setLeakDetectionThreshold(60000);

        return new com.zaxxer.hikari.HikariDataSource(config);
    }

    /**
     * 执行SQL查询
     * @param dataSourceKey 数据源标识
     * @param sql SQL语句
     * @param params 参数列表
     * @return 查询结果
     */
    public SqlExecutionResult executeQuery(String dataSourceKey, String sql, List<Object> params) {
        try {
            // 验证数据源
            if (!validDataSources.contains(dataSourceKey)) {
                return SqlExecutionResult.failure("非法数据源: " + dataSourceKey);
            }

            // 验证SQL安全性
            SqlValidationResult validation = validateSql(sql);
            if (!validation.isValid()) {
                return SqlExecutionResult.failure("SQL校验失败: " + validation.getMessage());
            }

            // 获取JdbcTemplate
            JdbcTemplate jdbcTemplate = jdbcTemplateMap.get(dataSourceKey);
            if (jdbcTemplate == null) {
                return SqlExecutionResult.failure("数据源未找到: " + dataSourceKey);
            }

            // 设置查询超时
            jdbcTemplate.setQueryTimeout(30);

            // 执行查询
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, params.toArray());

            // 获取列信息
            List<ColumnInfo> columns = getColumnMetadata(jdbcTemplate, sql, params);

            return SqlExecutionResult.success(result, columns, result.size());

        } catch (Exception e) {
            return SqlExecutionResult.failure("执行异常: " + e.getMessage());
        }
    }

    /**
     * 执行更新操作（INSERT, UPDATE, DELETE）
     * @param dataSourceKey 数据源标识
     * @param sql SQL语句
     * @param params 参数列表
     * @return 更新结果
     */
    public SqlUpdateResult executeUpdate(String dataSourceKey, String sql, List<Object> params) {
        try {
            // 验证数据源
            if (!validDataSources.contains(dataSourceKey)) {
                return SqlUpdateResult.failure("非法数据源: " + dataSourceKey);
            }

            // 验证SQL安全性（允许DML操作）
            SqlValidationResult validation = validateUpdateSql(sql);
            if (!validation.isValid()) {
                return SqlUpdateResult.failure("SQL校验失败: " + validation.getMessage());
            }

            // 获取JdbcTemplate
            JdbcTemplate jdbcTemplate = jdbcTemplateMap.get(dataSourceKey);
            if (jdbcTemplate == null) {
                return SqlUpdateResult.failure("数据源未找到: " + dataSourceKey);
            }

            // 执行更新
            int affectedRows = jdbcTemplate.update(sql, params.toArray());

            return SqlUpdateResult.success(affectedRows);

        } catch (Exception e) {
            return SqlUpdateResult.failure("执行异常: " + e.getMessage());
        }
    }

    /**
     * 验证查询SQL的安全性
     */
    private SqlValidationResult validateSql(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return new SqlValidationResult(false, "SQL不能为空");
        }

        String trimmedSql = sql.trim();

        // 检查是否以SELECT开头
        if (!trimmedSql.toUpperCase().startsWith("SELECT")) {
            return new SqlValidationResult(false, "只允许SELECT查询语句");
        }

        // 检查是否包含分号
        if (trimmedSql.contains(";")) {
            return new SqlValidationResult(false, "SQL中不能包含分号");
        }

        // 检查是否包含注释
        if (trimmedSql.contains("--") || trimmedSql.contains("/*") || trimmedSql.contains("*/")) {
            return new SqlValidationResult(false, "SQL中不能包含注释");
        }

        // 使用正则检查危险关键字
        if (SQL_PATTERN.matcher(sql).matches()) {
            return new SqlValidationResult(false, "SQL包含危险关键字");
        }

        return new SqlValidationResult(true, "SQL校验通过");
    }

    /**
     * 验证更新SQL的安全性
     */
    private SqlValidationResult validateUpdateSql(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return new SqlValidationResult(false, "SQL不能为空");
        }

        String trimmedSql = sql.trim();

        // 检查是否包含分号
        if (trimmedSql.contains(";")) {
            return new SqlValidationResult(false, "SQL中不能包含分号");
        }

        // 检查是否包含注释
        if (trimmedSql.contains("--") || trimmedSql.contains("/*") || trimmedSql.contains("*/")) {
            return new SqlValidationResult(false, "SQL中不能包含注释");
        }

        // 允许的DML语句
        String upperSql = trimmedSql.toUpperCase();
        if (!upperSql.startsWith("INSERT") && !upperSql.startsWith("UPDATE") && !upperSql.startsWith("DELETE")) {
            return new SqlValidationResult(false, "只允许INSERT、UPDATE、DELETE语句");
        }

        return new SqlValidationResult(true, "SQL校验通过");
    }

    /**
     * 获取列元数据信息
     */
    private List<ColumnInfo> getColumnMetadata(JdbcTemplate jdbcTemplate, String sql, List<Object> params) {
        try {
            return jdbcTemplate.query(sql + " LIMIT 1", (rs, rowNum) -> {
                List<ColumnInfo> columns = new ArrayList<>();
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount; i++) {
                    ColumnInfo column = new ColumnInfo();
                    column.setName(metaData.getColumnName(i));
                    column.setType(metaData.getColumnTypeName(i));
                    column.setLabel(metaData.getColumnLabel(i));
                    columns.add(column);
                }
                return columns;
            }).get(0);
        } catch (Exception e) {
            // 如果无法获取元数据，返回空列表
            return new ArrayList<>();
        }
    }

    /**
     * 测试数据源连接
     */
    public boolean testConnection(String dataSourceKey) {
        try {
            JdbcTemplate jdbcTemplate = jdbcTemplateMap.get(dataSourceKey);
            if (jdbcTemplate == null) {
                return false;
            }

            jdbcTemplate.execute("SELECT 1");
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 获取可用的数据源列表
     */
    public Set<String> getAvailableDataSources() {
        return new HashSet<>(validDataSources);
    }

    /**
     * SQL执行结果类
     */
    public static class SqlExecutionResult {
        private boolean success;
        private String message;
        private List<Map<String, Object>> data;
        private List<ColumnInfo> columns;
        private int rowCount;

        private SqlExecutionResult(boolean success, String message) {
            this.success = success;
            this.message = message;
        }

        private SqlExecutionResult(boolean success, List<Map<String, Object>> data, List<ColumnInfo> columns, int rowCount) {
            this.success = success;
            this.data = data;
            this.columns = columns;
            this.rowCount = rowCount;
        }

        public static SqlExecutionResult success(List<Map<String, Object>> data, List<ColumnInfo> columns, int rowCount) {
            return new SqlExecutionResult(true, data, columns, rowCount);
        }

        public static SqlExecutionResult failure(String message) {
            return new SqlExecutionResult(false, message);
        }

        // Getters and Setters
        public boolean isSuccess() { return success; }
        public void setSuccess(boolean success) { this.success = success; }
        public String getMessage() { return message; }
        public void setMessage(String message) { this.message = message; }
        public List<Map<String, Object>> getData() { return data; }
        public void setData(List<Map<String, Object>> data) { this.data = data; }
        public List<ColumnInfo> getColumns() { return columns; }
        public void setColumns(List<ColumnInfo> columns) { this.columns = columns; }
        public int getRowCount() { return rowCount; }
        public void setRowCount(int rowCount) { this.rowCount = rowCount; }
    }

    /**
     * SQL更新结果类
     */
    public static class SqlUpdateResult {
        private boolean success;
        private String message;
        private int affectedRows;

        private SqlUpdateResult(boolean success, String message) {
            this.success = success;
            this.message = message;
        }

        private SqlUpdateResult(boolean success, int affectedRows) {
            this.success = success;
            this.affectedRows = affectedRows;
        }

        public static SqlUpdateResult success(int affectedRows) {
            return new SqlUpdateResult(true, affectedRows);
        }

        public static SqlUpdateResult failure(String message) {
            return new SqlUpdateResult(false, message);
        }

        // Getters and Setters
        public boolean isSuccess() { return success; }
        public void setSuccess(boolean success) { this.success = success; }
        public String getMessage() { return message; }
        public void setMessage(String message) { this.message = message; }
        public int getAffectedRows() { return affectedRows; }
        public void setAffectedRows(int affectedRows) { this.affectedRows = affectedRows; }
    }

    /**
     * 列信息类
     */
    public static class ColumnInfo {
        private String name;
        private String type;
        private String label;

        // Getters and Setters
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
        public String getType() { return type; }
        public void setType(String type) { this.type = type; }
        public String getLabel() { return label; }
        public void setLabel(String label) { this.label = label; }
    }

    /**
     * SQL校验结果类
     */
    public static class SqlValidationResult {
        private boolean valid;
        private String message;

        public SqlValidationResult(boolean valid, String message) {
            this.valid = valid;
            this.message = message;
        }

        public boolean isValid() { return valid; }
        public String getMessage() { return message; }
    }
}

// 使用示例
class SqlExecutorExample {
    public static void main(String[] args) {
        // 这里演示如何使用工具类
        DynamicSqlExecutor executor = new DynamicSqlExecutor();

        // 模拟环境配置
//        org.springframework.core.env.Environment env = new org.springframework.mock.env.MockEnvironment();
//        env.setProperty("spring.datasource.db1.url", "jdbc:mysql://localhost:3306/test_db");
//        env.setProperty("spring.datasource.db1.username", "root");
//        env.setProperty("spring.datasource.db1.password", "password");
//        env.setProperty("spring.datasource.db1.driver-class-name", "com.mysql.cj.jdbc.Driver");
//
//        executor.environment = env;
        executor.initializeDataSources();

        // 执行查询
        List<Object> params = Arrays.asList(1, "test");
        DynamicSqlExecutor.SqlExecutionResult result = executor.executeQuery(
                "db1",
                "SELECT * FROM users WHERE id = ? AND name = ?",
                params
        );

        if (result.isSuccess()) {
            System.out.println("查询成功，返回 " + result.getRowCount() + " 行数据");
            for (Map<String, Object> row : result.getData()) {
                System.out.println(row);
            }
        } else {
            System.out.println("查询失败: " + result.getMessage());
        }
    }
}